﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Runtime.CompilerServices;
using MySql.Data.MySqlClient;

namespace FmcWebsite.Core
{

    public partial class DBConnect
    {
        static public IDbConnect UseMySql(string host, string database, string user, string password)
        {
            Database = new DBConnectMySql(host, database, user, password);
            return Database;
               
        }
    }

    class DBConnectMySql : IDbConnect
    {
        private MySqlConnection connection;
        private const string conn_string = "data source={0};database={1};user id={2};password={3}";

        public DBConnectMySql(string host, string database, string user, string password)
        {
            connection = new MySqlConnection(
                string.Format(conn_string, host, database, user, password));
            try
            {
                connection.Open();                
            }
            catch
            {
            }
        }

        #region IDbConnect Members

        [MethodImpl(MethodImplOptions.Synchronized)]
        public System.Data.DataTable Select(string cmd, IEnumerable<DbParameter> parameters)
        {
            var command = new MySqlCommand(cmd, connection);
            if (parameters != null)
            {
                foreach (DbParameter par in parameters)
                    command.Parameters.Add(par);
            }            
            var adapter = new MySqlDataAdapter();
            adapter.SelectCommand = command;
            var table = new DataTable();
            adapter.Fill(table);
            return table;            
        }

        public System.Data.DataTable Select(string cmd)
        {
            return Select(cmd, null);
        }

        public bool ExecDDL(string cmd)
        {
            return ExecDDL(cmd, null);
        }

        public void Close()
        {
            connection.Close();
        }

        private bool IsId(string name)
        {
            return name.EndsWith("id");
        }

        private bool IsInvalidId(string name, object value)
        {
            return (value == null) || ((value is Int32) && (Int16.Parse(value.ToString()) < 0) && IsId(name));
        }

        private bool IsInvalidDate(object value)
        {
            return (value is DateTime) && (DateTime.Parse(value.ToString()).Year < 1850);
        }

        private MySqlDbType GetParamType(object value)
        {
            if (value == null)
                return MySqlDbType.Int16;

            if (value is int)
                return MySqlDbType.Int16;

            if (value is String)
                return MySqlDbType.String;

            if (value is DateTime)
                return MySqlDbType.DateTime;

            if (value is Boolean)
                return MySqlDbType.Bit;

            if (value is byte[])
            {
                return MySqlDbType.LongBlob;
            }

            return MySqlDbType.String;
        }        

        public System.Data.Common.DbParameter CreateParameter(string name, object value)
        {
            var param =  new MySqlParameter(name, GetParamType(value));
            if ((value != null) && !IsInvalidId(name, value) && !IsInvalidDate(value))
                param.Value = value;
            else
            {
                param.IsNullable = true;
                param.Value = DBNull.Value;
            }
            return param;
        }


        public bool ExecDDL(string cmd, IEnumerable<System.Data.Common.DbParameter> parameters)
        {
            var command = new MySqlCommand(cmd, connection);
            if (parameters != null)
            {
                foreach (DbParameter par in parameters)
                    command.Parameters.Add(par);
            }
            command.ExecuteNonQuery();
            return true;
        }

        #endregion
    }
}
